import pandas as pd

# 读取所有文件
erp_clxx = pd.read_csv('D:/project-2024-AA/data/ERP_CLXX.csv')
erp_fhdxx = pd.read_csv('D:/project-2024-AA/data/ERP_FHDXX.csv')
erp_khxx = pd.read_csv('D:/project-2024-AA/data/ERP_KHXX.csv')
erp_zdxx = pd.read_csv('D:/project-2024-AA/data/ERP_ZDXX.csv')
sys_dict_data = pd.read_csv('D:/project-2024-AA/data/sys_dict_data.csv')
sys_user = pd.read_csv('D:/project-2024-AA/data/sys_user.csv')
erp_fhjl = pd.read_csv('D:/project-2024-AA/data/ERP_FHJL.csv')
erp_khxd = pd.read_csv('D:/project-2024-AA/data/ERP_KHXD.csv')

# 将相关列转换为字符串类型
erp_fhjl['khxd_id'] = erp_fhjl['khxd_id'].astype(str)
erp_fhjl['khxx_id'] = erp_fhjl['khxx_id'].astype(str)
erp_fhjl['zdxx_id'] = erp_fhjl['zdxx_id'].astype(str)
erp_fhjl['fhdxx_id'] = erp_fhjl['fhdxx_id'].astype(str)
erp_fhjl['clxx_id'] = erp_fhjl['clxx_id'].astype(str)
erp_fhjl['hplx'] = erp_fhjl['hplx'].astype(str)
erp_fhjl['cpgg'] = erp_fhjl['cpgg'].astype(str)
erp_fhjl['cppp'] = erp_fhjl['cppp'].astype(str)

erp_khxd['id'] = erp_khxd['id'].astype(str)
erp_khxd['create_by'] = erp_khxd['create_by'].astype(str)
erp_khxx['id'] = erp_khxx['id'].astype(str)
erp_zdxx['id'] = erp_zdxx['id'].astype(str)
erp_fhdxx['id'] = erp_fhdxx['id'].astype(str)
erp_clxx['id'] = erp_clxx['id'].astype(str)
sys_user['user_name'] = sys_user['user_name'].astype(str)
sys_dict_data['dict_value'] = sys_dict_data['dict_value'].astype(str)

# 先将 erp_fhjl 与 erp_khxd 合并，要用到creat——by
merged_df = pd.merge(erp_fhjl, erp_khxd, left_on='khxd_id', right_on='id', suffixes=('', '_khxd'))
print("After merging ERP_FHJL with ERP_KHXD: ", merged_df.shape)

# 然后继续合并其他表
merged_df = pd.merge(merged_df, erp_khxx, left_on='khxx_id', right_on='id', suffixes=('', '_khxx'))
print("After merging with ERP_KHXX: ", merged_df.shape)

merged_df = pd.merge(merged_df, erp_zdxx, left_on='zdxx_id', right_on='id', suffixes=('', '_zdxx'))
print("After merging with ERP_ZDXX: ", merged_df.shape)

merged_df = pd.merge(merged_df, erp_fhdxx, left_on='fhdxx_id', right_on='id', suffixes=('', '_fhdxx'))
print("After merging with ERP_FHDXX: ", merged_df.shape)

merged_df = pd.merge(merged_df, erp_clxx, left_on='clxx_id', right_on='id', suffixes=('', '_clxx'))
print("After merging with ERP_CLXX: ", merged_df.shape)

merged_df = pd.merge(merged_df, sys_user, left_on='create_by', right_on='user_name', suffixes=('', '_user'))
print("After merging with SYS_USER: ", merged_df.shape)

merged_df = pd.merge(merged_df, sys_dict_data[sys_dict_data['dict_type'] == 'ERP_HPLX'][['dict_value', 'dict_label']], left_on='hplx', right_on='dict_value', suffixes=('', '_hplx'))
print("After merging with SYS_DICT_DATA (HPLX): ", merged_df.shape)

merged_df = pd.merge(merged_df, sys_dict_data[sys_dict_data['dict_type'] == 'ERP_CPGG'][['dict_value', 'dict_label']], left_on='cpgg', right_on='dict_value', suffixes=('', '_cpgg'))
print("After merging with SYS_DICT_DATA (CPGG): ", merged_df.shape)

# 新增部分：合并 sys_dict_data 中的 cppp
merged_df = pd.merge(merged_df, sys_dict_data[sys_dict_data['dict_type'] == 'ERP_CPPP'][['dict_value', 'dict_label']], left_on='cppp', right_on='dict_value', suffixes=('', '_cppp'))
print("After merging with SYS_DICT_DATA (CPPP): ", merged_df.shape)

# 选择并重命名所需的列
result_df = merged_df[['id', 'create_time', 'user_name', 'ywlx_code', 'khmc', 'mc', 'zdmc', 'dict_label', 'dict_label_cpgg', 'dict_label_cppp', 'jz', 'dzdw', 'dj', 'hk', 'cph']]
result_df.columns = ['fhjl_id', 'fhjl_time', 'sales_name', 'ywlx_code', 'khmc', 'fhdmc', 'zdmc', 'hplx', 'cpgg', 'cppp', 'fhdw', 'dzdw', 'dj', 'hk', 'cph']

# 保存结果到新的 CSV 文件
result_df.to_csv('联合.csv', index=False, encoding='utf-8-sig')

# 检查结果数据框的大小
print("Resulting DataFrame shape: ", result_df.shape)








